/*
 * MembershipDAO.java
 *
 * Created on February 19, 2007, 7:08 PM
 *
 * @author Jeff Jenkins
 */

package edu.byu.isys413.jjenkins.data;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * This class contains methods to create, read, update and delete the
 * membership Object. It also contains methods to read an object by
 * serveral different criteria. All interaction between the object
 * and the database is done through this class.
 *
 * @author      Jeff Jenkins
 * @version     1.0
 *
 */
public class MembershipDAO {
    
    /**
     * singleton variable
     */
    private static MembershipDAO instance = null;
    
    
    /** Creates a new instance of MembershipDAO */
    private MembershipDAO() {
    }
    
    
    /** This method implements the singleton pattern. It insures that only
     * on instance of MembershipDAO exists
     *
     * @return MembershipDAO instance */
    
    public static synchronized MembershipDAO getInstance() {
        if (instance == null) {
            instance = new MembershipDAO();
        }
        return instance;
    }
    
    
    /**
     * This method creates a new Member BO, creates and sets the GUID, and sets
     * the cooresponding customer It then returns it to the calling method.
     * 
     * 
     * @return MemberBO: customer
     * @throws DataException 
     */
    
    public MemberBO create() throws DataException {
        try {
            String id = GUID.generate();
            MemberBO member = new MemberBO();
            member.setId(id);
            
            
            
            
            
            return member;
            
            
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     * 
     * 
     * @return MembershipBO
     * @param id 
     * @throws DataException 
     */
    public synchronized MemberBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        MemberBO member = (MemberBO)cache.get(id);
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(member == null){
                try {
                    //reads in customer
                    member = read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                }
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return Customer
        return member;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     * 
     * @return MemberBO
     * @param id 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    synchronized MemberBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        MemberBO member = (MemberBO)cache.get(id);
        
        // Quieries that database
        if(member == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM membership WHERE membership_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                member = new MemberBO();
                member.setId(rs.getString("membership_id"));
                member.setCreditCard(rs.getString("credit_card_num"));
                member.setStartDate(Date.valueOf(rs.getString("start_date")));
                member.setExpireDate(Date.valueOf(rs.getString("expire_date")));
                member.setBackupSize(rs.getDouble("backup_size"));
                member.setBackupNotice(rs.getDate("backup_notice"));
                System.out.println("about to get password");
                System.out.println("This is the password being read in: " + rs.getString("password"));
                member.setPassword(rs.getString("password"));
                member.setBackupEndDate(rs.getDate("backup_end_date"));
                System.out.println("jeff:  " + rs.getDate("backup_end_date"));
                
                
                
                
                
                // Gets membership areas of interest if it exists
                member.setAreasOfInterest(getMembershipAreasOfInterest(member.getId(), conn));
                
                
                // Sets already in database
                member.setObjectAlreadyInDB(true);
                
                // adds to cache
                cache.put(member.getId(), member);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns member
        return member;
    }
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     * 
     * 
     * @param member 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    public synchronized void save(MemberBO member) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Customer
                save(member, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for" + member.getId(), e);
            }
            
            // After all items are saved (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(member.getId());
            
            
            
            
        } catch (DataException e) {
            throw new DataException("Could not save record for" + member.getId(), e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + member.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + member.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     * 
     * 
     * @param member 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    synchronized void save(MemberBO member, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (member.isDirty()) {
            if (member.isObjectAlreadyInDB()) {
                update(member, conn);
            }else{
                insert(member, conn);
            }
            member.setDirty(false);
            
        }
        
        // saves the interests if they have become dirty
        if(member.isInterestDirty()){
            saveMembershipAreasOfInterest(member, conn);
        }
        
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * 
     * @param member 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    private synchronized void update(MemberBO member, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE membership SET membership_id = ?, credit_card_num = ?, start_date = ?, expire_date = ?, customer_id = ?, backup_size = ?, backup_notice = ?, password = ?, backup_end_date = ? WHERE membership_id = ?");
        pstmt.setString(1, member.getId());
        pstmt.setString(2, member.getCreditCard());
        pstmt.setDate(3, member.getStartDate());
        pstmt.setDate(4, member.getExpireDate());
        pstmt.setString(5, member.getCustomer().getId());
        pstmt.setDouble(6, member.getBackupSize());
        pstmt.setDate(7, member.getBackupNotice());
        pstmt.setString(8, member.getPassword());
        pstmt.setDate(9, member.getBackupEndDate());
        pstmt.setString(10, member.getId());
        
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * 
     * @param member 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    private synchronized void insert(MemberBO member, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO membership VALUES(?, ?, ?, ?, ?, ?, ?, ?,?)");
        pstmt.setString(1, member.getId());
        pstmt.setString(2, member.getCreditCard());
        pstmt.setDate(3, member.getStartDate());
        pstmt.setDate(4, member.getExpireDate());
        pstmt.setString(5, member.getCustomer().getId());
        pstmt.setDouble(6, member.getBackupSize());
        pstmt.setDate(7, member.getBackupNotice());
        pstmt.setString(8, member.getPassword());
        pstmt.setDate(9, member.getBackupEndDate());
        
        //Executes prepated statement
        pstmt.execute();
        
        //Sets already in database true
        member.setObjectAlreadyInDB(true);
        
    }
    
    
    /**
     * We intentionally do not allow a member object to be deleted because
     * it could mess up relationships in the database
     * @param customer 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    
    public synchronized void delete(CustomerBO customer)throws DataException{
        /* We intentionally do not allow a member object to be deleted because
         * it could mess up relationships in the database */
        throw new DataException("You can not delete a customer");
    }
    
    
    
    
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  The customer is passed in, that way if a membership is available for
     *  that customer, the membership property can be set in the customer object
     *  from this point. I choose to do it here that way when the customerDAO
     *  asks the membership DAO it doesn't have to check to see if one exits.
     *  That property exists in the Membership
     * 
     * 
     * @returns MemberBO
     * @param id 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return 
     */
    synchronized MemberBO readByCustomer(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        MemberBO member = (MemberBO)cache.get(id);
        
        // Quieries that database
        if(member == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM membership WHERE customer_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            
            if(rs.next()){
                member = new MemberBO();
                member.setId(rs.getString("membership_id"));
                member.setCreditCard(rs.getString("credit_card_num"));
                member.setStartDate(rs.getDate("start_date"));
                member.setExpireDate(rs.getDate("expire_date"));
                member.setBackupSize(rs.getDouble("backup_size"));
                member.setBackupNotice(rs.getDate("backup_notice"));
                member.setPassword(rs.getString("password"));
                member.setBackupEndDate(rs.getDate("backup_end_date"));
                
                
                // Gets membership areas of interest if it exists
                member.setAreasOfInterest(getMembershipAreasOfInterest(member.getId(), conn));
                
                
                // Sets already in database
                member.setObjectAlreadyInDB(true);
                
                
            }
        }
        
        
        // returns member
        return member;
    }
    
    
    /**
     * This method save all of the membership areas of interest. It
     * just deletes all of the membership areas of interest for a this 
     * member from the database then it readds them with the changes.
     * @param member 
     * @param conn 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    
    public void saveMembershipAreasOfInterest(MemberBO member, Connection conn)throws DataException{
        try {
            
            
            // Creates statement and populates list
            try {
                
                // deletes old list from database
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM membership_interest WHERE membership_id = ?");
                pstmt.setString(1, member.getId());
                pstmt.execute();
                
                // enters new list into database
                PreparedStatement pstmtSave = conn.prepareStatement("INSERT INTO membership_interest VALUES(?, ?)");
                
                // enters all of the members areas of interest into the database
                for(int i = 0; i < member.getAreasOfInterest().size(); i++){
                    pstmtSave.setString(1, member.getId());
                    pstmtSave.setString(2, member.getAreasOfInterest().get(i).toString());
                    pstmtSave.execute();
                }
                
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of interest " + e);
            }
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not save membership interest changes " + e);
        } catch (DataException e) {
            throw new DataException("Could not save membership interest changes " + e);
        }
        
    }
    
    
    /**
     *  This reads the areas of interest for a member and returns a list with the
     *  id.
     * @param member_id 
     * @param conn 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return 
     */
    public List getMembershipAreasOfInterest(String member_id, Connection conn) throws DataException{
        List list = new LinkedList();
        
        
        
        
        // Creates statement and populates list
        try {
            
            // prepares statemnt
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM membership_interest WHERE membership_id = ?");
            pstmt.setString(1, member_id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            
            while(rs.next()){
                list.add(rs.getString("interest_id"));
            }
            
        } catch (SQLException e) {
            try{
                conn.rollback();
                throw new DataException("Could not retrieve list of interest " + e);
            }catch(SQLException ex){
                throw new DataException("Could not retrieve list of customers " + ex);
            }}
        
        // Return Customer
        return list;
    }
}

